
package org.madason.humanresources.datalayer;

import java.util.*;
import java.sql.*;
import java.util.Date;

/**
 * 
 */
public class RegionAccessOracle extends RegionAccess
{
	protected java.sql.Timestamp convertDate(java.util.Date	date)
	{
		if (date == null) return null;
		else return new java.sql.Timestamp(date.getTime());
	}

	protected java.util.Date convertDate(java.sql.Timestamp	date)
	{
		if (date == null) return null;
		else return new java.util.Date(date.getTime());
	}
		
	
    /**
     * insert new Region
     */    
	public void insert(Connection conn, Region bean) throws Exception
	{
		// check whether bean is an subclass class
		
		insertIntern(conn, bean);
	}


    /**
     * internal implementation of insert
     */
	public void insertIntern(Connection conn, Region bean) throws Exception
	{

		String sqlCmd =
			"INSERT INTO \"REGIONS\" (\"REGION_ID\", \"REGION_NAME\") VALUES ( ?, ? )";

		PreparedStatement stm = null;

		try
		{
			
			stm = conn.prepareStatement(sqlCmd);
        
        

			stm.setInt(1, bean.getId());
			if (bean.name == null)
				stm.setNull(2, Types.VARCHAR);
			else
				stm.setString(2, bean.getName());
			
			stm.executeUpdate();
		}
		finally
		{
			if (stm != null) stm.close();
		}
	}
	

	public void delete(Connection conn, int primaryKey) throws Exception
	{			
		PreparedStatement stm = null;

		try
		{
			String sqlCmd =
				"DELETE FROM \"REGIONS\" WHERE \"REGION_ID\" = ?";

            stm = conn.prepareStatement(sqlCmd);

			stm.setInt(1, primaryKey);
			stm.executeUpdate();
		}
		finally
		{
			if (stm != null) stm.close();
		}
		
	}


	public void update(Connection conn, Region bean) throws Exception
	{
		// check whether bean is an subclass class
		
		updateIntern(conn, bean);
	}


	public void updateIntern(Connection conn, Region bean) throws Exception
	{				

		PreparedStatement stm = null;

		try
		{                    
			
			String sqlCmd =
				"UPDATE \"REGIONS\" "+
				"SET \"REGION_NAME\" = ? "+
				"WHERE \"REGION_ID\" = ? " +                    
				"";
			stm = conn.prepareStatement(sqlCmd);

			if (bean.getName() == null)				
				stm.setString(1, bean.getName());			
			else
				stm.setString(1, bean.getName());				
				


			stm.setInt(2, bean.getId());						
                    	                    
			int count = stm.executeUpdate();
			if (count == 0) throw new Exception("Der Datensatz wurde zwischzeitlich bearbeitet oder geloescht");
		}
		finally
		{
			if (stm != null) stm.close();
		}
	}
		
	

		
				
		
	
	public List<Region> findAll(Connection conn) throws Exception
	{
		List<Region> results = new ArrayList<Region>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"REGIONS\".\"REGION_ID\", \"REGIONS\".\"REGION_NAME\" FROM \"REGIONS\"";

			stm = conn.prepareStatement(sqlCmd);

			rs = stm.executeQuery();
											
			int idx_Region_id = rs.findColumn("REGION_ID");
			int idx_Region_name = rs.findColumn("REGION_NAME");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Region bean = Region.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Region_id));
				bean.setName(rs.getString(idx_Region_name));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public Region findByPrimaryKey(Connection conn, int id) throws Exception
	{

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"REGIONS\".\"REGION_ID\", \"REGIONS\".\"REGION_NAME\" FROM \"REGIONS\" WHERE \"REGIONS\".\"REGION_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setInt(1, id);
			rs = stm.executeQuery();
											
			int idx_Region_id = rs.findColumn("REGION_ID");
			int idx_Region_name = rs.findColumn("REGION_NAME");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Region bean = Region.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Region_id));
				bean.setName(rs.getString(idx_Region_name));
																																		
				
				return bean;
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return null;
	}		
	
	
}
  

	





